package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.dao.BooksDao;
import com.oocl.pojo.Books;
import com.oocl.util.DBUtil;

public class BooksDaoImpl implements BooksDao{
	 
	public int addBook(Books b) {
		String sql = "insert into books(id,name,pub_date,author,price,is_new,publisher) values(seq_book_id.nextval,?,?,?,?,?,?)";
		int count=0;
		Connection con = null;
		PreparedStatement stat =null;
		try {
			 con =  DBUtil.createConnection();
			 stat = con.prepareStatement(sql);
			stat.setString(1,b.getName());
			stat.setDate(2,new Date(b.getPubDate().getTime()));
			stat.setString(3,b.getAuthor());
			stat.setDouble(4,b.getPrice());
			stat.setBoolean(5,b.isNewBook());
			stat.setString(6,b.getPublisher());
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, null);
		}
		return count;
	}

	public int updateBook(Books b) {
		String sql = "update books set name = ?,pub_date=?,author=?,price=?,is_new=?,publisher=?  where id = ?";
		int count=0;
		Connection con = null;
		PreparedStatement stat =null;
		try {
			con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setString(1,b.getName());
			stat.setDate(2,new Date(b.getPubDate().getTime()));
			stat.setString(3,b.getAuthor());
			stat.setDouble(4,b.getPrice());
			stat.setBoolean(5,b.isNewBook());
			stat.setString(6,b.getPublisher());
			stat.setInt(7,b.getId());
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
			DBUtil.close(con, stat, null);
		}
		
		return count;
	}

	public int deleteBook(Integer id) {
		String sql = "delete books  where id = ?";
		int count =0;
		Connection con = null;
		PreparedStatement stat =null;
		try {
			con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setInt(1,id);
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, null);
			
		}
		return count;
	}

	public Books loadBook(Integer id) {
		String sql = "select * from books";
		Books b = null;
		Connection con = null;
		PreparedStatement stat =null;
		ResultSet rs = null;
        try {
        	con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			rs = stat.executeQuery(sql);
			if(rs.next()){
				b = new Books();
				b.setId(rs.getInt("id"));
				b.setName(rs.getString("name"));
				b.setPubDate(rs.getDate("pub_date"));
				b.setAuthor(rs.getString("author"));
				b.setPrice(rs.getDouble("price"));
				b.setNewBook(rs.getBoolean("is_new"));
				b.setPublisher(rs.getString("publisher"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, rs);
			
		}
        return b;
	}

	public List<Books> findAll() {
		String sql = "select * from books";
		List<Books> list = new ArrayList<Books>();
		Connection con = null;
		PreparedStatement stat =null;
		ResultSet rs = null;
        try {
        	con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			rs = stat.executeQuery(sql);
			while(rs.next()){
				Books book = new Books();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setPubDate(rs.getDate("pub_date"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setNewBook(rs.getBoolean("is_new"));
				book.setPublisher(rs.getString("publisher"));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, rs);
			
		}
		return list;
	}

	public int addBooks(List<Books> bs) {
		String sql = "insert into books(id,name,pub_date,author,price,is_new,publisher) values(seq_book_id.nextval,?,?,?,?,?,?)";
		int count=0;
		Connection con = null;
		PreparedStatement stat =null;
		try {
			 con =  DBUtil.createConnection();
			 con.setAutoCommit(false);
			stat = con.prepareStatement(sql);
			for(int i=0;i<bs.size();i++){
				Books b = bs.get(i);
				stat.setString(1,b.getName());
				stat.setDate(2,new Date(b.getPubDate().getTime()));
				stat.setString(3,b.getAuthor());
				stat.setDouble(4,b.getPrice());
				stat.setBoolean(5,b.isNewBook());
				stat.setString(6,b.getPublisher());
				stat.addBatch();
				//System.out.println(b);
				count++;
			}
			stat.executeBatch();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, null);
		}
		return count;
		
	}

	
	

}
